# postgres接入数据库
import psycopg2
from flask import jsonify
import creatToken
import numpy as np
#flask连接postgres数据库
def get_conn():
    # if "conn" not in g:
    conn = psycopg2.connect(database='bike', user='postgres', password='123456789',
        host='localhost', port='5432')
    conn.set_client_encoding('UTF-8')
    return conn

#插入数据库 test
def insert_data(user_id, name):  # 连接数据库并插入菜信息的数据库和sql函数
    conn = get_conn()
    print(conn)
    cur = conn.cursor()
    sql = "insert into ourfood (id,name) " \
          "values ('{0}','{1}');".format(user_id, name)
    print(sql)
    cur.execute(sql)
    conn.commit()
    print('更新成功')

#与数据库中的login表进行信息比对，登录系统
def check_user(user,password):
    conn = get_conn()
    print(conn)
    cur = conn.cursor()
    sql = "select password from login where username='{0}'".format(user)
    print(sql)
    cur.execute(sql)
    # 返回结果
    result = cur.fetchone()
    # 返回结果，并且结果为password
    if result and result[0]==password:
        conn.commit()
        print('查询成功！')
        #生成token
        userToken=creatToken.creatToken(user)
        print(userToken)
        return userToken
    else:
        conn.commit()
        print('查询失败！用户不存在或密码错误')
        return ''

#与数据库中的login表进行信息比对，保存用户注册的密码、用户名、邮箱
def register_user(user,email,password):
    #   判断数据库连接是否有错
    # pgErorr = False
    try: #捕抓连接中的错误
        #连接数据库
        conn=get_conn()
        print(conn)
        # 获取连接产生的错误
        cur=conn.cursor()
        # 执行语句
        sql="insert into login (username,email,password) values('{0}','{1}','{2}');".format(user, email,password)
        print(sql)
        cur.execute(sql)
        conn.commit()
    except(Exception,psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return''
        # pgErorr=True
    #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception,psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql",erorr)
        return ''
        # pgErorr=True
    else:#无异常关闭数据库
        cur.close()
        conn.close()
        return 'True'


# 获取轨迹点数据 gj20201221
def get_bikeTace(table):
    #   判断数据库连接是否有错
    # pgErorr = False
    try: #捕抓连接中的错误
        #连接数据库
        conn=get_conn()
        print(conn)
        # 获取连接产生的错误
        cur=conn.cursor()
        # 执行语句
        sql = "select gid,bicycle_id,latitude,longitude,date,time,ST_AsText(geom) from {0} where gid<=20000".format(table)
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content={
                'id':rv[0],
                'bikeID':rv[1],
                'lat':rv[2],
                'lon':rv[3],
                'date':rv[4],
                'time':rv[5],
                'point':rv[6]
            }
            payload.append(content)
        # for rv in result:
        #     content={
        #         'id':rv[0],
        #         'bicycle_id': rv[1],
        #         'date': rv[2],
        #         'time':rv[9],
        #         'lat': rv[3],
        #         'lon': rv[4],
        #         'source': rv[5],
        #         'geom': rv[11],
        #     }
        #     payload.append(content)

    except(Exception,psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return("Erorr while creating pgsql to table:",error)
        # pgErorr=True
    #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception,psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql",erorr)
        return("Erorr while connecting to pgsql",erorr)
        # pgErorr=True
    else:#无异常关闭数据库
        cur.close()
        conn.close()
        # print(payload)
        return jsonify(payload)

# 获取厦门路网数据
def get_xmRoad():
    #   判断数据库连接是否有错
    # pgErorr = False
    try: #捕抓连接中的错误
        #连接数据库
        conn=get_conn()
        print(conn)
        # 获取连接产生的错误
        cur=conn.cursor()
        # 执行语句
        sql = "select ST_AsText(geom) from xm_osm_road"
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content={
                'multiline':rv[0]
            }
            payload.append(content)
        # for rv in result:
        #     content={
        #         'id':rv[0],
        #         'bicycle_id': rv[1],
        #         'date': rv[2],
        #         'time':rv[9],
        #         'lat': rv[3],
        #         'lon': rv[4],
        #         'source': rv[5],
        #         'geom': rv[11],
        #     }
        #     payload.append(content)
    except(Exception,psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return("Erorr while creating pgsql to table:",error)
        # pgErorr=True
    #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception,psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql",erorr)
        return("Erorr while connecting to pgsql",erorr)
        # pgErorr=True
    else:#无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)

# 交通流密度
def get_xmRoad_trafficCounts():
    #   判断数据库连接是否有错
    # pgErorr = False
    try: #捕抓连接中的错误
        #连接数据库
        conn=get_conn()
        print(conn)
        # 获取连接产生的错误
        cur=conn.cursor()
        # 执行语句
        sql = "select gid,userid,road_id,road_name,traj_count,ST_AsText(geom) from xm_road_counts"
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content={
                'id':rv[0],
                'userid':rv[1],
                'road_id':rv[2],
                'road_name':rv[3],
                'traj_count':rv[4],
                'multiline':rv[5]
            }
            payload.append(content)
    except(Exception,psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return("Erorr while creating pgsql to table:",error)
        # pgErorr=True
    #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception,psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql",erorr)
        return("Erorr while connecting to pgsql",erorr)
        # pgErorr=True
    else:#无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)

# 获取指定大小的单车流量道路
def get_xmRoad_trafficCounts_tiaojian(count):
    #   判断数据库连接是否有错
    # pgErorr = False
    try: #捕抓连接中的错误
        #连接数据库
        conn=get_conn()
        print(conn)
        # 获取连接产生的错误
        cur=conn.cursor()
        # 执行语句
        sql = "select gid,userid,road_id,road_name,traj_count,ST_AsText(geom) from xm_road_counts where traj_count>={0}".format(count)
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content={
                'id':rv[0],
                'userid':rv[1],
                'road_id':rv[2],
                'road_name':rv[3],
                'traj_count':rv[4],
                'multiline':rv[5]
            }
            payload.append(content)
    except(Exception,psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return("Erorr while creating pgsql to table:",error)
        # pgErorr=True
    #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception,psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql",erorr)
        return("Erorr while connecting to pgsql",erorr)
        # pgErorr=True
    else:#无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)

# 统计指定大小单车流量道路的数量
def count_xmRoad_trafficCounts_tiaojian(count):
    #   判断数据库连接是否有错
    # pgErorr = False
    try: #捕抓连接中的错误
        #连接数据库
        conn=get_conn()
        print(conn)
        # 获取连接产生的错误
        cur=conn.cursor()
        # 执行语句
        sql = "select traj_count from xm_road_counts where traj_count>={0}".format(count)
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        #需要返回的结果数
        resultCount=int((1000-count)/100+1)
        name=['0-100','100-200','200-300','300-400','400-500','500-600','600-700','700-800','800-900','900-1000','1000以上']
        value=np.zeros(11)
        #统计value
        for rv in result:
            if rv[0]>0 and rv[0]<100:
                value[0]+=1
            elif rv[0]>=100 and rv[0]<200:
                value[1]+=1
            elif rv[0] >= 200 and rv[0] < 300:
                value[2] += 1
            elif rv[0] >= 300 and rv[0] < 400:
                value[3] += 1
            elif rv[0] >= 400 and rv[0] < 500:
                value[4] += 1
            elif rv[0] >= 500 and rv[0] < 600:
                value[5] += 1
            elif rv[0] >= 600 and rv[0] < 700:
                value[6] += 1
            elif rv[0] >= 700 and rv[0] < 800:
                value[7] += 1
            elif rv[0] >= 800 and rv[0] < 900:
                value[8] += 1
            elif rv[0] >= 900 and rv[0] < 1000:
                value[9] += 1
            elif rv[0] >= 1000 :
                value[10] += 1
        # 将结果转为JSON格式
        payload = []
        for i in range(resultCount):
            content={
                'value':value[i+11-resultCount],
                'name':name[i+11-resultCount],
                'valueClass':count + i*100+1
            }
            payload.append(content)
    except(Exception,psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return("Erorr while creating pgsql to table:",error)
        # pgErorr=True
    #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception,psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql",erorr)
        return("Erorr while connecting to pgsql",erorr)
        # pgErorr=True
    else:#无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)


#查询停车点位
def get_bikeParkPoint():
    #   判断数据库连接是否有错
    # pgErorr = False
    try: #捕抓连接中的错误
        #连接数据库
        conn=get_conn()
        print(conn)
        # 获取连接产生的错误
        cur=conn.cursor()
        # 执行语句
        sql = "select id,belong_are,center_x,center_y,bike_remain,ST_AsText(geom) from bike_fence"
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content={
                'id':rv[0],
                'road_name':rv[1],
                'centerx':rv[2],
                'centery':rv[3],
                'bike_remain':rv[4],
                'multipolygon':rv[5]
            }
            payload.append(content)
    except(Exception,psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return("Erorr while creating pgsql to table:",error)
        # pgErorr=True
    #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception,psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql",erorr)
        return("Erorr while connecting to pgsql",erorr)
        # pgErorr=True
    else:#无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)
#泰森多边形
def get_tession():
    # pgErorr = False
    try:  # 捕抓连接中的错误
        # 连接数据库
        conn = get_conn()
        print(conn)
        # 获取连接产生的错误
        cur = conn.cursor()
        # 执行语句
        sql = "select gid,area,o_count,d_count,remain,density,ST_AsText(geom) from teissen"
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content = {
                'gid': rv[0],
                'area': rv[1],
                'o_count': rv[2],
                'd_count': rv[3],
                'remain': rv[4],
                'density':rv[5],
                'multipolygon':rv[6]
            }
            payload.append(content)
    except(Exception, psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return ("Erorr while creating pgsql to table:", error)
        # pgErorr=True
    #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception, psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql", erorr)
        return ("Erorr while connecting to pgsql", erorr)
        # pgErorr=True
    else:  # 无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)


#查询1221 OD点
def get_ODPoint(table):
    #   判断数据库连接是否有错
    # pgErorr = False
    try: #捕抓连接中的错误
        #连接数据库
        conn=get_conn()
        print(conn)
        # 获取连接产生的错误
        cur=conn.cursor()
        # 执行语句
        sql = "select gid,bicycle_id,lock_statu,update_tim,ST_AsText(geom) from {0}".format(table)
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content={
                'gid':rv[0],
                'bikeID':rv[1],
                'lock_statu':rv[2],
                'update_tim':rv[3],
                'point':rv[4]
            }
            payload.append(content)
    except(Exception,psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return("Erorr while creating pgsql to table:",error)
        # pgErorr=True
    #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception,psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql",erorr)
        return("Erorr while connecting to pgsql",erorr)
        # pgErorr=True
    else:#无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)

#get photo
def get_streetphoto(x,y):
    #   判断数据库连接是否有错
    # pgErorr = False
    try:  # 捕抓连接中的错误
        # 连接数据库
        conn = get_conn()
        print(conn)
        # 获取连接产生的错误
        cur = conn.cursor()
        # 执行语句
        #sql = "select id,x,y,img_0,img_45, img_90,img_135,img_180,img_225,img_270,img_315 from streetphoto where id=1"
        sql="select id,x,y,img_0,img_45, img_90,img_135,img_180,img_225,img_270,img_315,ST_Length("\
        "ST_MakeLine("\
        "ST_MakePoint(x,y),"\
        "ST_MakePoint({0},{1}) "\
        ")::geography"\
        ") as distance from streetphoto  ORDER BY distance asc LIMIT 1".format(x,y)
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content = {
                'id': rv[0],
                'x': rv[1],
                'y': rv[2],
                'img_0': rv[3],
                'img_45': rv[4],
                'img_90': rv[5],
                'img_135': rv[6],
                'img_180': rv[7],
                'img_225': rv[8],
                'img_270': rv[9],
                'img_315': rv[10],
                'distance':rv[11]
            }
            payload.append(content)
    except(Exception, psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return ("Erorr while creating pgsql to table:", error)
        # pgErorr=True
    #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception, psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql", erorr)
        return ("Erorr while connecting to pgsql", erorr)
        # pgErorr=True
    else:  # 无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)


# 单车调度 区域
def get_dispatchArea():
    try:  # 捕抓连接中的错误
        # 连接数据库
        conn = get_conn()
        print(conn)
        # 获取连接产生的错误
        cur = conn.cursor()
        # 执行语句
        sql = "select gid,area,o_count,d_count,remain,ST_AsText(geom),lng,lat,fangan from first_remain"
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content = {
                'gid': rv[0],
                'area': rv[1],
                'o_count': rv[2],
                'd_count': rv[3],
                'remain': rv[4],
                'multipolygon': rv[5],
                'lng':rv[6],
                'lat':rv[7],
                'fangan':rv[8]
            }
            payload.append(content)
    except(Exception, psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return ("Erorr while creating pgsql to table:", error)
        # pgErorr=True
        #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception, psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql", erorr)
        return ("Erorr while connecting to pgsql", erorr)
        # pgErorr=True
    else:  # 无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)

# 单车调度 第一层调度站点
def get_firDispatchPoint():
    try:  # 捕抓连接中的错误
        # 连接数据库
        conn = get_conn()
        print(conn)
        # 获取连接产生的错误
        cur = conn.cursor()
        # 执行语句
        sql = "select gid,ST_AsText(geom) from first_station"
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content = {
                'gid': rv[0],
                'point': rv[1],
            }
            payload.append(content)
    except(Exception, psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return ("Erorr while creating pgsql to table:", error)
        # pgErorr=True
        #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception, psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql", erorr)
        return ("Erorr while connecting to pgsql", erorr)
        # pgErorr=True
    else:  # 无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)

# 第一层调度路线 /firstDisLine
def get_dispatchLine():
    try:  # 捕抓连接中的错误
        # 连接数据库
        conn = get_conn()
        print(conn)
        # 获取连接产生的错误
        cur = conn.cursor()
        # 执行语句
        sql = "select gid,ST_AsText(geom) from first_break"
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content = {
                'gid': rv[0],
                'multiline': rv[1],
            }
            payload.append(content)
    except(Exception, psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return ("Erorr while creating pgsql to table:", error)
        # pgErorr=True
        #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception, psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql", erorr)
        return ("Erorr while connecting to pgsql", erorr)
        # pgErorr=True
    else:  # 无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)

# 单车调度 第二层区域
def get_secDispatchArea(id):
    try:  # 捕抓连接中的错误
        # 连接数据库
        conn = get_conn()
        print(conn)
        # 获取连接产生的错误
        cur = conn.cursor()
        # 执行语句
        sql = "select gid,area,o_count,d_count,remain,ST_AsText(geom),lng,lat,fangan from second_renew where first_id={0}".format(id)
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content = {
                'gid': rv[0],
                'area': rv[1],
                'o_count': rv[2],
                'd_count': rv[3],
                'remain': rv[4],
                'multipolygon': rv[5],
                'lng':rv[6],
                'lat':rv[7],
                'fangan':rv[8]
            }
            payload.append(content)
    except(Exception, psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return ("Erorr while creating pgsql to table:", error)
        # pgErorr=True
        #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception, psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql", erorr)
        return ("Erorr while connecting to pgsql", erorr)
        # pgErorr=True
    else:  # 无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)

# 单车调度 第二层路线
def get_secDispatchLine(id):
    try:  # 捕抓连接中的错误
        # 连接数据库
        conn = get_conn()
        print(conn)
        # 获取连接产生的错误
        cur = conn.cursor()
        # 执行语句
        sql = "select gid,ST_AsText(geom) from second_break where first_id={0}".format(id)
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content = {
                'gid': rv[0],
                'multiline': rv[1],
            }
            payload.append(content)
    except(Exception, psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return ("Erorr while creating pgsql to table:", error)
        # pgErorr=True
        #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception, psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql", erorr)
        return ("Erorr while connecting to pgsql", erorr)
        # pgErorr=True
    else:  # 无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)

# 单车调度 第二层调度站点
def get_secDispatchPoint(id):
    try:  # 捕抓连接中的错误
        # 连接数据库
        conn = get_conn()
        print(conn)
        # 获取连接产生的错误
        cur = conn.cursor()
        # 执行语句
        sql = "select gid,ST_AsText(geom) from points_second where first_id={0}".format(id)
        print(sql)
        cur.execute(sql)
        conn.commit()
        # 返回结果
        result = cur.fetchall()
        # 将结果转为JSON格式
        payload = []
        content = {}
        for rv in result:
            content = {
                'gid': rv[0],
                'point': rv[1],
            }
            payload.append(content)
    except(Exception, psycopg2.DatabaseError) as error:
        print("Erorr while creating pgsql to table", error)
        return ("Erorr while creating pgsql to table:", error)
        # pgErorr=True
        #     如果有错误，则捕抓然后大于错误，这里是无法连接时的报错
    except(Exception, psycopg2.Error) as erorr:
        print("Erorr while connecting to pgsql", erorr)
        return ("Erorr while connecting to pgsql", erorr)
        # pgErorr=True
    else:  # 无异常关闭数据库
        cur.close()
        conn.close()
        print(payload)
        return jsonify(payload)